<?php
/**
 * Created by PhpStorm.
 * User: Steven
 * Date: 2017/5/24
 */

namespace app\student\controller;


use app\classes\model\ClassModel;
use app\classes\model\ClassSeatModel;
use app\classes\model\ClassStudentModel;
use app\classroom\model\ExcelModel;
use app\course\model\CourseModel;
use app\educational\model\MessageModel;
use app\financial\model\OrderModel;
use app\system\model\SchoolModel;
use app\portal\model\UserModel;
use app\student\model\StudentModel;
use app\student\model\StudentVisitModel;
use cmf\controller\AdminBaseController;
use think\Db;
use think\Exception;
use PHPExcel;

class StudentController extends AdminBaseController
{
    /**
     * 学员列表
     * @return mixed
     */
    public function index()
    {
        $param = $this->request->param();
        if(empty($_SESSION['student_filter']))
        {
            unset($_SESSION['student_filter']);
        }
        $_SESSION['student_filter']=$param;
        $limit = config('limit_per_page');
        $where = [];
        //店铺ID
//        $where['s.shop_id'] = ['=', $this->admin_shop_id];

        if (!empty($param['name'])) {
            //学生姓名
            $where['s.name'] = ['like', '%' . trim($param['name']) . '%'];
        }
        if (!empty($param['parent_name'])) {
            //家长姓名
            $where['u.user_login|u.realname|u.user_nickname'] = ['like', '%' . trim($param['parent_name']) . '%'];
        }
        if (!empty($param['code'])) {
            //学号
            $where['s.code |s.old_code'] = ['like', '%' . trim($param['code']) . '%'];
        }
        if (!empty($param['mobile']) && is_numeric($param['mobile'])) {
            //手机号
            $where['s.mobile'] = ['like', '%' . trim($param['mobile']) . '%'];
        }
       $where1=[];
        if (!empty($param['school_id'])) {
            //校区
            $where1['sc.id'] =  trim($param['school_id']);
            $where['sc.id'] =  trim($param['school_id']);
        }
        if (!empty($param['course_id'])&& is_numeric($param['course_id'])) {
            //课程
            $where['co.id'] =  trim($param['course_id']);
            $where1['co.id'] =  trim($param['course_id']);

        }
        if (!empty($param['class_id']) && is_numeric($param['class_id'])) {
            //班级
            $where1['ct.id'] =  trim($param['class_id']);
            $where['ct.class_id'] =  trim($param['class_id']);
        }
        if (!empty($param['is_read']) && is_numeric($param['is_read'])) {
            //在读状态
            $where['is_read'] =  trim($param['is_read']);
        }
        if (!empty($param['information_sources_type'])) {
            //信息来源
            $where['information_sources_type'] =  ['like', '%' . trim($param['information_sources_type']) . '%'];
        }

        //分页url额外参数
        $page_query = [
            'name' => isset($param['name']) ? $param['name'] : '',
            'parent_name' => isset($param['parent_name']) ? $param['parent_name'] : '',
            'code' => isset($param['code']) ? $param['code'] : '',
            'mobile' => isset($param['mobile']) ? $param['mobile'] : '',
            'class_id' => isset($param['class_id']) ? $param['class_id'] : '',
            'course_id' => isset($param['course_id']) ? $param['course_id'] : '',
            'school_id' => isset($param['school_id']) ? $param['school_id'] : '',


        ];
        if (isset($_SESSION['arr_error']) && !empty($_SESSION['arr_error'])) {

            $this->assign('session', $_SESSION['arr_error']);
        }


        /*
       * 学校（搜索框的校区)
       */
        $school_data=Db::name('school')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->where('is_school', '=', 0)//1:地区 0:学校
            ->select();
        /*
         * 课程分类(搜索框的课程）
         */
        $course_data=Db::name('course')
            ->where('shop_id', '=', $this->admin_shop_id)//多店铺
            ->where('delete_time', '=', 0)//未删除
            ->select();


        //班级（搜索)或者课程分类都是空的话，搜索框的班级也不显示
        if (empty($param['course_id']) && empty($param['school_id'])) {
            $data_class=Db::name('class')->alias('c')->field('name cname,c.id cid ')
                ->where('shop_id', '=', $this->admin_shop_id)//多店铺
                ->where('delete_time', '=', 0)//未删除
                ->select()->toArray();
        }else {  //如果有校区和课程搜索
            //搜索框的班级
            $data_class = Db::name('class')->alias('ct')
                ->field('ct.id cid,ct.name cname')
                ->join('school sc', 'ct.school_id=sc.id')
                ->join('course co', 'ct.course_id=co.id')
                ->where($where1)
                ->order('ct.id')
                ->select()
                ->toArray();
        }

        $room_model = new StudentModel();


        $data = $room_model->getStudents($where, [], $limit, $page_query);
        $count=$room_model->getStudentsWhere($where);
        $total=$room_model->total();
        $this->assign('course_data', $course_data); //课程
        $this->assign('school_data', $school_data); //校区
        $this->assign('param', $param);
        $this->assign('data_class',$data_class); //搜索的班级
        $this->assign('students', $data);
        $this->assign('count',$count);
        $this->assign('total',$total);
        $this->assign('page', $data->render());
        return $this->fetch();
    }

    /*
     * 清楚缓存
     */
    public function clean()
    {
        unset($_SESSION['arr_error']);
        return $this->redirect('Student/index');

    }

    /*
     * 添加学生
     */
    public function add()
    {
        return $this->fetch();
    }

    public function addPost()
    {
        $cur_time = time();
        $validate = validate('Student');
        if ($this->request->isPost()) {
            $data = $this->request->except('pid');
            //家长id
            $pid = $this->request->post('pid');

            if (!$validate->check($data)) {
                $this->error($validate->getError());
            }

            if (empty(trim($data['name']))) {
                $this->error('学生姓名不能为空');
            }
            if (empty(trim($pid)) || !is_numeric($pid)) {
                $this->error('家长手机号不能为空');
            }
            if (empty(trim($data['address']))) {
                $this->error('联系地址不能为空');
            }

            $student_mdl = new StudentModel();
            $total = $student_mdl->where(['shop_id' => $this->admin_shop_id])
                ->where(['delete_time' => 0])
                ->where(['name' => trim($data['name'])])
                ->where(['mobile' => trim($data['mobile'])])
                ->count();
            if (isset($total) && $total >= 1) {
                $this->error('该学生已存在');
            }

            //家长id
            $mobile = trim($data['mobile']);
            $user = Db::name('user')->where(['mobile'=>$mobile,'user_type'=>2])->find();
            if(count($user) <= 0){
                $this->error('家长不存在');
            }

            if (!empty($data['birthday'])) {
                //生日
                $data['birthday'] = strtotime($data['birthday']);
            }

            //学号
            $data['code'] = $student_mdl->generateCode();
            $data['u_id'] = $pid;
            $data['name'] = trim($data['name']);
            $data['address'] = trim($data['address']);
            $data['create_time'] = $cur_time;

            $id = $student_mdl->allowField(true)->insertGetId($data);
            if ($id) {
                //记录日志
                $pk = $id; //主键
                $content = lang('ADD_STUDENT', ['name' => $data['name']]);
                do_log($content, $pk, 'student', $cur_time);

                $this->success('添加成功', 'student/index', '', 2);
            } else {
                $this->error("添加失败");
            }
        }
    }

    /**
     * 模糊搜索用户(Ajax自动补全功能)
     * @return \think\response\Json
     */
    public function ajaxUser()
    {
        $data = [
            '没有匹配的结果'
        ];

        $param = $this->request->param();
        if(empty($param['query'])){
            $json_data = [
                'query' => 'Unit',
                'suggestions' => $data
            ];
            return json($json_data);
        }

        //获取班级
        $where = [
            'u.shop_id' => ['eq', $this->admin_shop_id],
            'u.user_type' => ['eq', 2],//家长
            'u.user_status' => ['eq', 1],//正常启用
            'u.mobile' => ['like','%'.trim($param['query']).'%'],
        ];

        //获取管理员
        $user_mdl = new UserModel();
        $result = $user_mdl->alias('u')->field('u.id as data,u.mobile as value')
            ->where($where)
            ->order('u.id','asc')
            ->select();
        if(count($result) > 0){
            $data = $result;
        }

        $json_data = [
            'query' => 'Unit',
            'suggestions' => $data
        ];
        return json($json_data);
    }

    /*
     * 编辑学生
     */
    public function edit($id)
    {
        if (empty($id)) {
            $this->error('参数不能为空');
        }
        $model = new StudentModel();
        $data = $model->where('id', $id)->find();
        if (!isset($data->id)) {
            $this->error('没有该学生信息');
        }

        $this->assign('data', $data);
        return $this->fetch();
    }

    /*
     * 保存编辑学生
     */
    public function editPost()
    {
        $cur_time = time();
        $url = 'student/index';
        //验证
        $validate = validate('Student');
        if ($this->request->isPost()) {

            $data = $this->request->except('code');
            $id = $data['id'];
            if (!$validate->check($data)) {
                $this->error($validate->getError());
            }

            if (empty(trim($data['name']))) {
                $this->error('学生姓名不能为空');
            }
            if (empty(trim($data['pid'])) || !is_numeric($data['pid'])) {
                $this->error('家长手机号不能为空');
            }
            if (empty(trim($data['address']))) {
                $this->error('联系地址不能为空');
            }

            $model = new StudentModel();
            $filter_id = [
                'id' => ['eq', intval($id)],
                'shop_id' => ['eq', $this->admin_shop_id]
            ];
            $result = $model->getStudentByField($filter_id);
            if (isset($result->id) && empty($result->id)) {
                $this->error('没有该学生', $url);
            }

            //检查学生是否已存在
            $filter = [
                'u_id' => ['eq', trim($data['pid'])],
                'name' => ['eq', trim($data['name'])],
                'shop_id' => ['eq', $this->admin_shop_id]
            ];
            $res = $model->getStudentByField($filter);
            if (isset($res->id) && ($res->id != $id)) {
                $this->error('该学生已存在');
            }

            //家长id
            $mobile = trim($data['mobile']);
            $user = Db::name('user')->where(['mobile'=>$mobile,'user_type'=>2])->find();
            if(count($user) <= 0){
                $this->error('家长不存在');
            }

            if (!empty($data['birthday'])) {
                //生日
                $data['birthday'] = strtotime($data['birthday']);
            }

            $data['u_id'] = $data['pid'];
            $data['create_time'] = $cur_time;
            $data['update_time'] = $cur_time;

            $res = $model->editStudent($data);
            if ($res) {
                //记录日志
                $pk = $id; //主键
                $content = lang('EDIT_STUDENT', ['name' => $data['name']]);
                do_log($content, $pk, 'student', $cur_time);

                $this->success('编辑成功', $url, '', 2);
            } else {
                $this->error("编辑失败");
            }
        }
    }

    /**
     *删除学生
     */
    public function delete()
    {
        $cur_time = time();
        $table = 'student';
        $student_mdl = new StudentModel();

        $param = $this->request->param();
        if (isset($param['id'])) {
            $id = $this->request->param('id', 0, 'intval');
            $studentName = db::name("student")->where('id', $id)->value('name');
            //记录日志
            $pk = $id;
            $content = lang('DELETE_STUDENT', ['name' => $studentName]);
        } else {
            $id = $this->request->param('ids/a');
            //记录日志
            $pk = implode(',', $id);
            $content = lang('BATCH_DELETE', ['name' => '学生']);
        }

        $result = $student_mdl->deleteStudent($id, $cur_time);
        if ($result) {
            do_log($content, $pk, $table, $cur_time);

            $this->success("删除成功！", '');
        } else {
            $this->error('删除失败！');
        }
    }

    /**
     *excel的批量的导入信息
     */
    public function excel()
    {
        $type = pathinfo($_FILES['pic']['name'], PATHINFO_EXTENSION);
        //  echo $type;
        $allow_type = ['xls', 'xlsx'];
        if (!in_array($type, $allow_type)) {
            $this->error('上传不是execle文件');
        }

        import('phpexcel.PHPExcel', EXTEND_PATH);
        import('phpexcel.PHPExcel.IOFactory', EXTEND_PATH);
        import('phpexcel.PHPExcel.Shared',EXTEND_PATH);

        $student_model = new StudentModel();
        $last_id = $student_model->max('id');
        $last_code = $student_model->where(['id' => $last_id])->value('code');
        $student_model = new StudentModel();
        //所有学生的信息
        $arr_student = $student_model->where(['delete_time' => 0])->select()->toArray();
        //$所有错误的信息的数据
        $arr_error = [];
//        $filename=($_SERVER['DOCUMENT_ROOT'] .'/upload/11.xls');//文件的路径
        $filereturn = $this->upload('pic', $dir = $_SERVER['DOCUMENT_ROOT'] . '/upload/');

        $dir = substr($filereturn, 0, 8);
        $filepath = ($_SERVER['DOCUMENT_ROOT'] . '/upload/' . $dir . '/' . $filereturn);

        $fileType = \PHPExcel_IOFactory::identify($filepath);
        $preg = '/Excel/';
        $a = preg_match_all($preg, $fileType, $arr1);
        if (!$a) {
            $this->error('上传的格式不对');
        }
        $objPHPExcel = \PHPExcel_IOFactory::load($filepath);//加载文件
        /*$sheetCount=$objPHPExcel->getSheetCount();//获取当前excel有多少sheet
        for($i=0;$i<$$sheetCount;$i++){
            $data=$objPHPExcel->getSheet($i)->toArray();
        }
        */
        $arr = [];
        foreach ($objPHPExcel->getWorksheetIterator() as $sheet) { //循环sheet
            foreach ($sheet->getRowIterator() as $kk => $row) { //逐行处理
                foreach ($row->getCellIterator() as $k => $cell) { //逐行读取

                    $data = $cell->getValue();//获取单元格数据
                    if ($k == 'H') {
                        $data = gmdate("Y-m-d", \ PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()));
                    }
                    $arr[$kk][] = $data;
                }
            }
        }
        foreach ($arr[1] as $k => $v) {

            if ($v == '学号') {
                $old_code = $k;
            }
            if ($v == '姓名') {
                $name = $k;
            }
            if ($v == '年级') {
                $grade = $k;
            }
            if ($v == '性别') {
                $gener = $k;
            }
            if ($v == '出生日期') {
                $birthday = $k;
            }
            if ($v == '母亲手机') {
                $moblie = $k;
            }
            if ($v == '所在学校') {
                $school_name = $k;
            }

        }
        unset($arr[1]);//删除第一行
        $data_student = [];
        $data_parent = [];
        $data_parent_arr = [];
        foreach ($arr as $k => &$v) {

            $data_student['is_old_student']=1; //导入就是老学员
            if (!empty($v[$old_code]) && isset($old_code)) {
                $data_student['old_code'] = $v[$old_code];
            }

            $data_student['code'] = intval($last_code) + $k;

            if (!isset($name)) {
                $this->error('请在第一行增加姓名的列');

            }else{
                $data_student['name'] = $v[$name];

            }
            if (isset($gener)) {
                if ($v[$gener] == '男') {
                    $data_student['gender'] = 1;
                } else {
                    $data_student['gender'] = 2;
                }
            }
            if (isset($grade)) {
                $data_student['grade'] = $v[$grade];
            }
            if (isset($school_name)) {
                $data_student['school_name'] = $v[$school_name];
            }
            if (!isset($moblie)) {
                $this->error('请在第一行增加家长手机号的列');
            }else{
                $data_student['mobile'] = $v[$moblie];
            }
            if (isset($birthday)) {
                $time = \PHPExcel_Shared_Date::ExcelToPHP($v[$birthday]);
                $data_student['birthday'] = $time;
            }
            $data_parent_arr[$k] = $data_student;


            //是否重复导入数据，重复了就不导入进去了
            $repeat_child_model = $student_model->where(['mobile' => $v[$moblie]])->where('name', "$v[$name]")->find();
            if (empty($v[$name])) {
                unset($data_parent_arr[$k]);
            }
            if (empty($v[$moblie]) && !empty($v[$name])) {
                $arr_error[$k] = '第' . $k . '行家长手机号为空';
                unset($data_parent_arr[$k]);
            }
            if (!empty($repeat_child_model)) {
                if (!empty($repeat_child_model['old_code'])) {
                    $arr_error[$k] = '第' . $k . '行重复的数据';
                    unset($data_parent_arr[$k]);
                } else {
                    Db::name('student')->where(['mobile' => $v[$moblie]])->update(['old_code' => $v[$old_code]]);
                    $arr_error[$k] = '此用户是老学员';
                    unset($data_parent_arr[$k]);
                }
            }

        }

        if(count($data_parent_arr)<1)
        {
            $this->error('没有可以导入的数据啊');
        }
        $ids = [];
        Db::startTrans();
        try {
            foreach ($data_parent_arr as $k=>$v) {
                //user中父母
                $id=Db::name('user')->where(['mobile'=>$v['mobile']])->value('id');
                if(empty($id))
                {
                    $v['u_id']=0;
                }else{
                    $v['u_id']=$id;
                }
                $ids[] = Db::name('student')->insertGetId($v);
            }
            Db::commit();
//            $id = implode(',', $ids);
//            $pk = $id; //主键
//            $table = 'student';//数据表名
//            $content = '导入新的学生成功和修改已有老学员成功';
//            do_log($content, $pk, $table, time());

            unset($_SESSION['arr_error']);
            $_SESSION['arr_error'] = $arr_error;
            $this->success('添加成功', 'student/index', $arr_error, 2);
        } catch (Exception $e) {
            Db::rollback();
            unset($_SESSION['arr_error']);
            $_SESSION['arr_error'] = $arr_error;;

            $this->error('导入失败', 'student/index', $arr_error, 2);
        }
    }

    function upload($name, $dir = './upload/', $allow_type = array('xls', 'xlsx'))
    {

        if ($_FILES[$name]['error'] > 0) {
            /* switch($_FILES['pic']['error']){
                 case 1:
                     echo'';

            }*/
            echo '上传错误';
            return false;
        }
        //2.获取后缀名
        $type = pathinfo($_FILES[$name]['name'], PATHINFO_EXTENSION);
        //  echo $type;
        if (!in_array($type, $allow_type)) {
            echo '上传一个execel过来';
            return false;
        }
        //3.起名字
        $filename = date('Ymd') . uniqid() . mt_rand(0, 9999) . '.' . $type;

        //4.保存路径

        $save_path = rtrim($dir, '/');
        $save_path .= '/';
        $save_path .= date('Ymd');
        // echo $save_path;
        if (!file_exists($save_path)) {
            mkdir($save_path, '0777', true);
            chmod($save_path, '0755');
        }
        $path = $save_path . '/' . $filename;

        if (!is_uploaded_file($_FILES[$name]['tmp_name'])) {
            echo '回去！ 别来玩了！';
            return false;
        }
        if (!move_uploaded_file($_FILES[$name]['tmp_name'], $path)) {
            echo '在来一边 没有移动了';
        }

        return $filename;

    }

    //回访记录的index页面
    public function visit()
    {
        $tid = $this->request->param("tid");
        $class_id = $this->request->param('class_id');
        $old_class_id = $this->request->param('old_class_id', 0);
        $model = new StudentVisitModel();
        $is_and_class = Db::name('class')->alias('cs')->where('cs.id', $class_id)->value('is_and_class');

        if (!empty($is_and_class)) {
            $visit = $model->where(['delete_time' => 0, 'student_id' => $tid, 'class_id' => $old_class_id])->select()->toArray();
            $class_id = $old_class_id;
        } else {
            $visit = $model->where(['delete_time' => 0, 'student_id' => $tid, 'class_id' => $class_id])->select()->toArray();
        }


        foreach ($visit as $k => $v) {
            $v['content'] = trim(htmlspecialchars_decode($v['content']), '');
            $visit[$k] = $v;
        }
        $this->assign('class_id', $class_id);
        $this->assign('student_id', $tid);

        $this->assign('visit', $visit);

        return $this->fetch();
    }

    //添加的回访记录
    public function addvisit()
    {
        //所有的老师
        $class_id = $this->request->param('class_id');
        $student_id = $this->request->param('student_id');
        $teacher_model = new UserModel();
        $teacher_tree = $teacher_model->where(['user_type' => '1', 'user_status' => '1'])->field(['id', 'user_login'])->select()->toArray();

        $student_model = new StudentModel();
        $student_tree = $student_model->where(['delete_time' => 0])->field(['id', 'name'])->select()->toArray();

        $this->assign('teacher_tree', $teacher_tree);
        $this->assign('student_tree', $student_tree);

        $this->assign('class_id', $class_id);
        $this->assign('student_id', $student_id);
        return $this->fetch();
    }

    //addvistpost
    public function addVisitPost()
    {
        $class_id = $this->request->param('class_id');
        $tid = $this->request->param('student_id');
        $old_class_id = Db::name('class_student')->alias('cs')->where('cs.class_id', $class_id)->value('old_class_id');
        $model = new StudentVisitModel();
        $post = $this->request->post();
        $post['create_time'] = time();
        $id = $model->insertGetId($post);

        if ($id) {

            $pk = $id; //主键
            $table = 'student_visit';//数据表名
            //记录日志
            $name = Db::name('student')->where(['id' => $tid])->value('name');
            $content = lang('ADD_STUDENT_VISIT', ['name' => $name]);
            do_log($content, $pk, $table, time());
            $this->success('添加访问成功', url('Student/visit', array('class_id' => $class_id, 'tid' => $tid, 'old_class_id' => $old_class_id)));
        }
        $pk = $id; //主键
        $table = 'student_visit';//数据表名
        //记录日志
        $content = '添加学生回访失败';
        $this->error('添加访问回访失败');
    }

    //添加的回访记录
    public function editvisit()
    {
        $id = $this->request->param('id');
        $student_model = new StudentVisitModel();
        $data = $student_model->where(['delete_time' => 0, 'id' => $id])->find();
        $data['content'] = trim(htmlspecialchars_decode($data['content']), '');
        $this->assign('data', $data);
        return $this->fetch();
    }

    //addvistpost
    public function editVisitPost()
    {
        $model = new StudentVisitModel();
        $id = $this->request->param('id');
        $class_id = $model->where('id', $id)->value('class_id');
        $tid = $model->where('id', $id)->value('student_id');
        $old_class_id = Db::name('class_student')->alias('cs')->where('cs.class_id', $class_id)->value('old_class_id');

        $post = $this->request->post();
        $post['update_time'] = time();

        $result = $model->allowField(true)->isUpdate(true)->data($post, true)->save(['id' => $id]);

        if ($result) {
            $pk = $id; //主键
            $table = 'student_visit';//数据表名
            //记录日志
            $name = Db::name('student')->where(['id' => $tid])->value('name');
            $content = lang('EDIT_STUDENT_VISIT', ['name' => $name]);
            do_log($content, $pk, $table, time());
            $this->success('编辑访问成功', url('Student/visit', array('class_id' => $class_id, 'tid' => $tid, 'old_class_id' => $old_class_id)));
        }
        $this->error('编辑访问失败');
    }
    /*
   * 递归一个函数连报,首先判断连报的课程的 is_class enable_class_ids
   */
    public function enable_class($class_id, $data = [])
    {
        $class_data = Db::name('class')->where(['id' => $class_id])->find();
        $is_class = $class_data['is_class'];
        $enable_class = $class_data['enable_class_ids'];
        if (!empty($is_class)) {
            if (!empty($enable_class)) {
                $class_id = intval(explode(',', $enable_class)[0]);
                $data[] = $class_id;
                $data = array_merge($data, $this->enable_class($class_id));
            }
        }else{
            $data=[];
        }
        return $data;
    }

    public function deleteVisit()
    {

        $ids = $this->request->param('ids/a');
        $model = new StudentVisitModel();
        if (!is_array($ids)) {
            $id = $this->request->param('id', 0, 'intval');
            //开始事务
            Db::startTrans();
            try {
                $student_id = Db::name('student_visit')->where('id', $id)->value('student_id');
                $res = $model->where('id', intval($id))->update(['delete_time' => time()]);
                Db::commit();
                $pk = $id; //主键
                $table = 'student_visit';//数据表名
                //记录日志
                $name = Db::name('student')->where(['id' => $student_id])->value('name');
                $content = lang('DELETE_STUDENT_VISIT', ['name' => $name]);
                do_log($content, $pk, $table, time());
                $this->success('删除成功');
            } catch (Exception $e) {
                Db::rollback();
                $this->error('删除失败');
            }
        } else {
            Db::startTrans();
            try {
                $res = $model->where('id', 'in', $ids)->update(['delete_time' => time()]);
                Db::commit();
                $id = implode(',', $ids);
                $pk = $id; //主键
                $table = 'student_visit';//数据表名
                //记录日志
                $content = '批量删除学生回访成功';
                do_log($content, $pk, $table, time());
                $this->success('删除成功');
            } catch (Exception $e) {
                Db::rollback();
                $this->error('删除失败');
            }
        }

    }

    /**
     *学员的详情
     */
    public function view()
    {
        $param = $this->request->param();
        $id = $param['id'];
        $class_model = new ClassModel();

        if(!isset($id) || empty($id) || !is_numeric($id)){
            $this->error('请选择学生');
        }

        //学员报班信息
        $data = Db::name('class_student')->alias('cs')->distinct('cs.id')
            ->field('t.name tname ,t.id tid, c.name cname,c.id cid,c.school_id,c.signup_price,c.class_status,cs.progress as left_lesson,cs.class_id,
            cs.is_refuse,cs.status csstatus,cs.insert_class_time,od.amount ctotal_price,co.course_hour,co.course_name,co.id coid,od.id as order_id,
            od.bill_status,od.order_status,od.pay_amount,od.continue_year_type,cs.delete_time csdelete_time')
            ->join('student t', 't.id=cs.student_id ')
            ->join('class c', 'c.id=cs.class_id')
            ->join('course co', 'co.id=cs.course_id')
            ->join('order od', 'cs.shop_id=od.shop_id and cs.class_id=od.class_id and cs.student_id=od.student_id',$type='left')
            ->where(['cs.student_id' => $id,'c.delete_time'=>0])
            ->where(['cs.shop_id' => $this->admin_shop_id])
//            ->where('od.order_status', ['eq', 2], ['eq', 1], ['eq', 4], 'or')
            ->select()
            ->toArray();
        foreach ($data as $k => &$v) {
            //所有课程的课程进度和改变课程的状态
            $arr = $class_model->progress($data[$k]['cid']);
            $arr = json_decode($arr, True);
            $data[$k]['progress'] = $arr['progress'];
            /**************学生名字及回访内容****************/
            $model = new StudentVisitModel();
            $student_visit = $model->where('student_id', $id);
            //回访
            $v['visit'] = $student_visit->where('class_id', $v['cid'])->select()->toarray();
            //处理课程状态;
            if ($v['class_status'] == 0) {
                $v['class_status'] = '待读';
            } else if ($v['class_status'] == 1) {
                $v['class_status'] = '在读';
            } else {
                $v['class_status'] = '已读';
            }
        }
        if (count($data) > 0) {
            foreach ($data as $k => &$v) {
                //所有课程的课程进度和改变课程的状态
                $arr = $class_model->progress($data[$k]['cid']);
                $arr = json_decode($arr, True);
                $data[$k]['progress'] = $arr['progress'];
//                dump($arr);
//                dump(count($data));
                //处理学生姓名;
                $student_name = $data[$k]['tname'];
                if(!is_null($v['order_status'])){
                    if($v['order_status']==0 || $v['order_status']==3) {
                        //如果连报，删除没有支付订单和1年连报
                        if ($v['continue_year_type'] == 2) {
                            $continue_id = $this->enable_class($v['cid'])[0];
//                            $continue_class=Db::name('order')->where(['student_id'=>$student_id,'class_id'=>$continue_id])->find();
                            foreach ($data as $j => $val) {
                                if ($val['cid'] == $continue_id) {
                                    unset($data[$j]);
                                }
                            }
                        }
                        if ($v['continue_year_type'] == 3) {
                            $continue_id = $this->enable_class($v['cid'])[0];
                            $continue_id1 = $this->enable_class($v['cid'])[1];
//                            $continue_class=Db::name('order')->where(['student_id'=>$student_id,'class_id'=>$continue_id])->find();
                            foreach ($data as $j => $val) {
                                if ($val['cid'] == $continue_id) {
                                    unset($data[$j]);
                                }
                                if ($val['cid'] == $continue_id1) {
                                    unset($data[$j]);
                                }
                            }
                            unset($data[$k]);
                        }
                         unset($data[$k]);
                    }

                    if($v['order_status']!==4 &&  $v['csdelete_time']!==0) {
                        unset($data[$k]);

                    }

                }



            }
            $count=count($data);

            if(!empty($data1))
            {
                $data3[$count]=$data1;
                $data=array_merge($data,$data3);

            }
            if(!empty($data2))
            {
                $data4[$count]=$data2;
                $data=array_merge($data,$data4);

            }

//            exit();

            //计算课时
            foreach ($data as $k => &$item){
                //连报数
                if($item['continue_year_type'] <= 0){
                    $continue = 1;
                }else{
                    $continue = $item['continue_year_type'];
                }
                //课程总课时数
                $item['total_num'] = $continue * intval($item['course_hour']);
                //最初报名时已上课时数
                $item['signup_used'] = $item['left_lesson'];
                //现在已上过的课时数
                $item['used_lesson'] = $item['progress'];

                //连报班级id
                $class_ids = $this->enable_class($item['cid']);
                if(!empty($class_ids)){
                    if ($item['continue_year_type'] == 2) {
                        //两册连报
                        foreach ($data as $key=>$val){
                            if($val['cid'] == $class_ids[0]){
                                //最初报名时已上课时数
                                $item['signup_used'] += $val['left_lesson'];
                                //现在已上过的课时数
                                $item['used_lesson'] += $val['progress'];
                            }
                        }
                    }elseif($item['continue_year_type'] == 3){
                        //三册连报
                        foreach ($data as $key=>$val){
                            if($val['cid'] == $class_ids[0] || $val['cid'] == $class_ids[1]){
                                //已上课时数(最初报名时)
                                $item['signup_used'] += $val['left_lesson'];
                                //现在已上过的课时数
                                $item['used_lesson'] += $val['progress'];
                            }
                        }
                    }
                }
            }

        } else {
            //处理学生姓名;
            $student_name = Db::name('student')->where(['id' => $id])->value('name');
        }
        //请假信息
        $student_leave = Db::name('student_leave')->alias('cs')
            ->field('cs.create_time,c.name cname,co.course_name,cb.name adjustcname')
            ->join('class c', 'c.id=cs.class_id')
            ->join('course co', 'co.id=cs.course_id')
            ->join('student_adjust sa','sa.leave_id=cs.id','left')
            ->join('class cb','cb.id=sa.class_id','left')
            ->where(['cs.student_id' => $id])
            ->where(['c.shop_id' => $this->admin_shop_id])
            ->select()
            ->toArray();

        //学生活动操作的记录
        $studen_active=Db::name('log_student_active')->where(['student_id'=>$id])->order('create_time DESC')->select()->toArray();

        $school_mdl = new SchoolModel();
        $schools = $school_mdl->getAllSchool($this->admin_shop_id);

        $course_mdl = new CourseModel();
        $courses = $course_mdl->where(['delete_time'=>0])->select()->toArray();

        $this->assign('schools', $schools);
        $this->assign('courses', $courses);

        $this->assign('tname', $student_name);

        $this->assign('data', $data);
        $this->assign('studen_active', $studen_active);

        $this->assign('id', $id);
        $this->assign('student_leave', $student_leave);

        return $this->fetch();
    }

    /*
     *申请退课
     */
    public function refund()
    {
        if ($this->request->isPost()){
            $post = $this->request->post();
            if (empty($post['way'])) {
                $this->error('请选择退课方式');
            }
            if (empty(trim($post['content']))) {
                $this->error('请填写退课原因');
            }
            if (empty($post['order_id'])) {
                $this->error('参数错误');
            }

            //学生id
            $student_id = trim($post['student_id']);
            //班级id
            $class_id = trim($post['class_id']);
            //获取班级
            $class = Db::name('class')->find($class_id);
            if(count($class) <= 0){
                $this->error('没有该班级');
            }

            //订单id
            $order_id = trim($post['order_id']);
            //获取订单
            $order_mdl = new OrderModel();
            $where = [
                'o.shop_id' => $class['shop_id'],
                'o.id' => $order_id,
                'o.class_id' => $class_id,
                'o.student_id' => $student_id,
            ];
            $order_info = $order_mdl->getOrderByField($where);
            if(count($order_info) <= 0){
                $this->error('学生不在该班级内');
            }

            /****由于管理员可以手动填写退费金额，故不能再重新计算*******/
//            //获取进度
//            $class_mdl = new ClassModel();
//            $progress = json_decode($class_mdl->progress($class_id), true)['progress'];
//            //获取课程总课时
//            $course = Db::name('course')->find($class['course_id']);
//            //剩余课时
//            $left_hours = $course['course_hour'] - $progress;
//            //退费金额
//            $refund_money = $order_info['pay_amount'] / $course['course_hour'] * intval($left_hours);
//            if($order_info['bill_status'] == 1){
//                //需要开发票,并且退费金额大于税费金额
//                if($refund_money > trim($post['tax'])){
//                    //减去税费
//                    $refund_money -= trim($post['tax']);
//                }
//            }

            //退课信息
            $refund_data = [
                'shop_id' => $order_info['shop_id'],
                'order_id' => $order_id,
                'applicant' => cmf_get_current_admin_id(),//申请人
                'student_id' => $student_id,
                'class_id' => $class_id,
                'content' => trim($post['content']),
                'way' => $post['way'],
                'tax' => empty(trim($post['tax'])) ? 0 : trim($post['tax']),
                'discount' => empty(trim($post['discount'])) ? 0.00 : trim($post['discount']),
                'refuse_price' => trim($post['refuse_price']),
                'status' => 1,//申请退费
                'create_time' => time(),
            ];

            Db::startTrans();
            try {
                //新建退费
                Db::name('order_refund')->insert($refund_data);

                //从班级中删除学生，并标记为退费中
                $condition = [
                    'student_id' => $student_id,
                    'class_id' => $class_id,
                    'shop_id' => $this->admin_shop_id,
                ];

                //记录当前学生状态
                $student_status = Db::name('class_student')->where($condition)->value('student_status');
                $_SESSION['student_status'] = $student_status;

                Db::name('class_student')->where($condition)->update(['delete_time' => time(),'student_status'=>2,'is_refuse' => 1]);

                //查看座位
                $seat_where = [
                    'student_id' => $student_id,
                    'classes_id' => $class_id,
                    'shop_id' => $this->admin_shop_id,
                ];
                //删除座位
                Db::name('class_seat')->where($seat_where)->update(['delete_time' => time()]);
                //班级报名人数减一
                Db::name('class')->where(['id' => $class_id])->setDec('current_num');

                Db::commit();

            }catch(Exception $e){
                Db::rollback();
                $this->error('申请退课失败');
            }

            //记录日志
            $pk = $student_id; //主键
            $table = 'order_refund,class,class_student,class_seat';//数据表名
            $name = Db::name('student')->where(['id' => $student_id])->value('name');
            $content = lang('TUI_CLASS', ['name' => $name, 'classname' => $class['name']]);
            do_log_student_active($content, $pk, $table, time(),$student_id);

            $this->success('申请退课成功');
        }else{
            $this->error('请求错误');
        }
    }

    /*
     * ajx_class_shcool
     */
    public function ajax_class()
    {
        if ($this->request->isPost()) {
            $param = $this->request->post();

            if (empty($param['school_id'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '学校不能为空',
                    'data' => []
                ];
                return json($json_data);
            }
            $school_id=$param['school_id'];

            //获取班级
            $where = [
                'c.shop_id' => ['eq', $this->admin_shop_id],
                'school_id' => ['eq', $param['school_id']],
                'co.delete_time'=>0,
                'c.delete_time'=>0,
            ];

            $data=Db::name('class')->distinct('co.course_name')->alias('c')
                ->field('co.course_name,co.id')
                ->join('school s','s.id=c.school_id')
                ->join('course co','co.id=c.course_id')
                ->where($where)
                ->where(['s.id'=>$param['school_id']])
                ->select()
                ->toArray();
            if (count($data) < 0) {
                $json_data = [
                    'code' => 0,
                    'msg' => '没有更多数据',
                    'school_id' => $param['school_id'],
                    'data' => $data
                ];
                return json($json_data);
            }

            $json_data = [
                'code' => 1,
                'msg' => 'success',
                'school_id' => $param['school_id'],
                'data' => $data
            ];
            return json($json_data);
        }
    }
    /*
      * ajx_class_shcool
      */
    public function ajax_course_class()
    {
        if ($this->request->isPost()) {
            $param = $this->request->post();

            if (empty($param['course_id'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '学校不能为空',
                    'data' => []
                ];
                return json($json_data);
            }

            //获取班级
            $where = [
                'shop_id' => ['eq', $this->admin_shop_id],
                'course_id' => ['eq', $param['course_id']],
                'is_and_class' => ['eq', 0],
                'class_status'=>['neq',2],
                'school_id'=>['eq', $param['school_id']],
                'delete_time'=>0
            ];
            $class_mdl = new ClassModel();
            $data = $class_mdl->where($where)->select()->toArray();

            if (count($data) < 0) {
                $json_data = [
                    'code' => 0,
                    'msg' => '没有更多数据',
                    'course_id' => $param['course_id'],
                    'data' => $data
                ];
                return json($json_data);
            }

            $json_data = [
                'code' => 1,
                'msg' => 'success',
                'course_id' => $param['course_id'],
                'data' => $data
            ];
            return json($json_data);
        }
    }

    public function calculateCost()
    {
        if ($this->request->isPost()) {

            $param = $this->request->post();
            if (empty($param['class_id'])) {
                $json_data = [
                    'code' => 0,
                    'msg' => '请选择班级',
                    'data' => []
                ];
                return json($json_data);
            }
            //原班级的id
            $org_cid = $param['orginal_cid'];
            //新班级的id
            $cid = $param['class_id'];

            $class_model = new ClassModel();
            //此班级的课程进度


            //查看课时
            $old_lesson_schema=Db::name('course_schedule')->where(['class_id'=>$org_cid])->order('date','asc')->select();
            if($old_lesson_schema->isEmpty())
            { //课时为空的情况
                return  $jsonData=[
                    'code'=>2,
                    'cost'=>111
                ];
            }
            $old_lesson_schema = $old_lesson_schema->toArray(); //转化数据
            $num=count($old_lesson_schema); //取得最后一个课时日期
            $first_lesson_progress=$old_lesson_schema[0]['date'];
            $last_lesson_progress=$old_lesson_schema[$num-1]['date'];


            //查看课时
            $new_lesson_schema=Db::name('course_schedule')->where(['class_id'=>$cid])->order('date','asc')->select();

            if($new_lesson_schema->isEmpty())
            { //课时为空的情况
                $jsonData=[
                    'code'=>21212,
                    'cost'=>111
                ];
                return json_encode($jsonData);
            }
            if(!$new_lesson_schema->isEmpty())
            {
                $new_lesson_schema = $new_lesson_schema->toArray(); //转化数据
            }
            $new_num=count($new_lesson_schema); //取得最后一个课时日期




            $new_first_lesson_progress=$new_lesson_schema[0]['date'];
            $new_last_lesson_progress=$new_lesson_schema[$new_num-1]['date'];
            $cur_time=time();

            foreach ($new_lesson_schema as $k => $v)
            {

                if ($cur_time > $new_last_lesson_progress)
                {
                    $this->error('班级结课了喽,是不是转错了');
                }
                else if($cur_time>$new_lesson_schema[$k]['date'] && $cur_time<$new_lesson_schema[$k+1]['date'])
                {
                    $new_price_total = $class_model->where(['id' => $cid])->value('lesson_price');
                    $new_course_id = $class_model->where(['id' => $cid])->value('course_id');
                    $length = Db::name('course')->where(['id' => $new_course_id])->value('course_hour');
                    //后期改成cmf_class_course
                    $progress=$new_lesson_schema[$k]['schedule'];

                    //剩余课时总钱数
                    $new_progress = $new_lesson_schema[$k]['schedule'];
                    $new_price = $new_price_total - ($new_price_total / $length) * $new_progress;

                } else if($cur_time<$new_first_lesson_progress)
                {
                    $new_price=$class_model->where(['id'=>$cid])->value('lesson_price');
                    $new_progress=0;
                }

            }
            foreach($old_lesson_schema as $k=>$v)
            {
                if($cur_time>=$last_lesson_progress)
                {
                    $price=$class_model->where(['id'=>$cid])->value('lesson_price');
                    $jsonData=[
                        'code'=>2,
                        'cost'=>$price
                    ];
                }
                else if($cur_time>$old_lesson_schema[$k]['date'] && $cur_time<$old_lesson_schema[$k+1]['date'])
                {
                    $old_price=$class_model->where(['id'=>$org_cid])->value('lesson_price');
                    $course_id=$class_model->where(['id'=>$org_cid])->value('course_id');
                    $length=Db::name('course')->where(['id'=>$course_id])->value('course_hour');
                    //后期改成cmf_class_course
                    $progress=$old_lesson_schema[$k]['schedule'];

                    //剩余课时总钱数
                    $left=$old_price-($old_price/$length)*$progress;

                    $price=$new_price-$left;
                    $jsonData=[
                        'code'=>4,
                        'cid'=>$cid,
                        'cost'=>$price,
                        'progress'=>$progress,
                        'left'=>$left,
                        'new_price'=>$new_price,
                        'old_price'=>$old_price,

                    ];


                }else if($cur_time<$first_lesson_progress)
                {

                    $old_price=$class_model->where(['id'=>$org_cid])->value('lesson_price');
                    $price=intval($new_price)-intval($old_price);
                    $jsonData=[
                        'code'=>3,
                        'cost'=>$price
                    ];

                }

            }
//            dump($jsonData);
            return json_encode($jsonData);


        }
    }
    /*
     * 转班
     */
    public function change_class()
    {
        //计算人数class ,座位信息删除，class_student信息
        if($this->request->isPost())
        {
            $post=$this->request->post();

            if(empty($post['class_id']))
            {
                 $this->error('班级不存在');
            }
            $old_class_id=$post['old_change_old_ids'];
            $new_class_id=$post['class_id'];
            $student_id=$post['student_id'];
            $student_name=Db::name('student')->where(['id'=>$student_id])->value('name');
            $parent_id=Db::name('student')->alias('t')
                ->join('user u','u.id=t.u_id')
                ->where(['t.id'=>$student_id])
                ->field('u.id')
                ->find();

            $cost=$post['cost'];

            $class_model=new ClassModel();
            $class_seat_model=new ClassSeatModel();
            $class_student_model=new ClassStudentModel();
            $order_model=new OrderModel();
            $user_model=new UserModel();
            $balance=$user_model->where(['id'=>$parent_id['id']])->value('balance');
            if(empty($balance))
            {
                $balance=0;
            }
            $new_num=$class_student_model->where(['class_id'=>$new_class_id])->count();
            $class_data=$class_model->where(['id'=>$new_class_id])->find();
            $total_num=$class_data['total_num'];
            if($new_num>=$total_num)
            {
                $this->error('此班级满员了');
            }

            //更新班级的人数
            Db::startTrans();
            try{
                //更新
                $course_id=$class_model->where(['id'=>$new_class_id])->value('course_id');
                $class_name=$class_model->where(['id'=>$new_class_id])->value('new_class_id');
                $class_student_model->where(['class_id'=>$old_class_id,'student_id'=>$student_id])->update(['class_id'=>$new_class_id,'old_class_id'=>$old_class_id,'status' => 1,'student_status'=>2,'course_id'=>$course_id]);
                $class_seat_model->where(['classes_id'=>$old_class_id,'student_id'=>$student_id])->update(['delete_time'=>time()]);
                $new_num=$class_student_model->where(['class_id'=>$new_class_id])->count();
                $old_num=$class_student_model->where(['class_id'=>$old_class_id])->count();
                $class_model->where(['id'=>$old_class_id])->update(['current_num'=>$old_num]);
                $class_model->where(['id'=>$new_class_id])->update(['current_num'=>$new_num]);
                if($cost>0)
                {
                    $order=[];
                    $order['order_id']=$order_model->generate_code();
                    $order['student_id']=$student_id;
                    $order['parent_id']=$parent_id['id'];
                    $order['class_id']=$new_class_id;
                    $order['class_name']=$class_name;
                    $order['amount']=$cost;
                    $order['pay_amount']=$cost;

//                    $order['pay_amount']=number_format($cost-$balance,2,'.','');
//                    if( $order['pay_amount']<=0)
//                    {  //剩下余额了
//                        $order['order_status']=1;
//                        $order['pay_amount']=0;
//                        $order['payment']=5;
//                        $balance=$balance-$order['pay_amount'];
//                        $user_model->where(['id'=>$parent_id['id']])->update(['balance'=>$balance]);
//                    }else{ //大于0说明余额全花掉了
//                        $balance=0;
//                        $user_model->where(['id'=>$parent_id['id']])->update(['balance'=>$balance]);
//                    }
                    $order['create_time'] = time();
                    $order['cancel_time'] = time() + config('order_expire_time');
                    $order_model->insert($order);
                }else if($cost<=0)
                {
                    $order=[];
                    $order['order_id']=$order_model->generate_code();
                    $order['student_id']=$student_id;
                    $order['order_status']=1;
                    $order['parent_id']=$parent_id['id'];
                    $order['class_id']=$new_class_id;
                    $order['payment']=5;
                    $order['class_name']=$class_name;
                    $order['pay_amount']=$cost;
                    $order['pay_time']=time();
                    $order['create_time'] = time();
                    $order['cancel_time'] = time() + config('order_expire_time');
                    $order_model->insert($order);
                    $balance=Db::name('user')->where(['id'=>$parent_id['id']])->value('balance');//原来的钱
                    $balance=$balance+abs($cost);
                   Db::name('user')->where(['id'=>$parent_id['id']])->update(['balance'=>$balance]);
                }


                $pk=$old_class_id;
                $table='class&class_seat&class_student&order';
                $newclass=$class_model->where(['id'=>$new_class_id])->value('name');
                $oldclass=$class_model->where(['id'=>$old_class_id])->value('name');
                $content = lang('CHANGE_CLASS', ['oldname' => $oldclass, 'newname' => $newclass]);
                do_log_student_active($content, $pk, $table, time(),$student_id);

                //前台发送消息

                $recive_user=[];
                $recive_user[] = ['id' =>$student_id ];
                $Message_model=new MessageModel();
                $data=[];
                $data['shop_id']=$this->admin_shop_id;
                $data['send_uid']=$student_id;
                $data['subject']='转班';
                $data['content']=$student_name."$oldclass"."转到".$newclass;
                $data['type']=0;
                $data['create_time']=time();
                $Message_model->sendMsg($data,$recive_user);

                 Db::commit();
                  $this->success('转班成功');
            }catch (Exception $e)
            {
                Db::rollback();

                  $this->error('转班失败');
            }
        }
    }

    //导出excel
    public function excel_export()
    {
        import('phpexcel.PHPExcel', EXTEND_PATH);
        import('phpexcel.PHPExcel.IOFactory', EXTEND_PATH);
        import('phpexcel.PHPExcel.Shared',EXTEND_PATH);

        $objPHPExcel = new PHPExcel();

        if(!empty($_SESSION['student_filter']))
        {
            $param=($_SESSION['student_filter']);
        }
        $limit = config('limit_per_page');
        $where = [];
        //店铺ID
//        $where['s.shop_id'] = ['=', $this->admin_shop_id];

        if (!empty($param['name'])) {
            //学生姓名
            $where['s.name'] = ['like', '%' . trim($param['name']) . '%'];
        }
        if (!empty($param['parent_name'])) {
            //家长姓名
            $where['u.user_login|u.realname|u.user_nickname'] = ['like', '%' . trim($param['parent_name']) . '%'];
        }
        if (!empty($param['code'])) {
            //学号
            $where['s.code |s.old_code'] = ['like', '%' . trim($param['code']) . '%'];
        }
        if (!empty($param['mobile']) && is_numeric($param['mobile'])) {
            //手机号
            $where['s.mobile'] = ['like', '%' . trim($param['mobile']) . '%'];
        }
        $where1=[];
        if (!empty($param['school_id'])) {
            //校区
            $where1['sc.id'] =  trim($param['school_id']);
            $where['sc.id'] =  trim($param['school_id']);
        }
        if (!empty($param['course_id'])&& is_numeric($param['course_id'])) {
            //课程
            $where['co.id'] =  trim($param['course_id']);
            $where1['co.id'] =  trim($param['course_id']);

        }
        if (!empty($param['class_id']) && is_numeric($param['class_id'])) {
            //班级
            $where1['ct.id'] =  trim($param['class_id']);
            $where['ct.class_id'] =  trim($param['class_id']);
        }
        if (!empty($param['is_read']) && is_numeric($param['is_read'])) {
            //在读状态
            $where['is_read'] =  trim($param['is_read']);
        }
        if (!empty($param['information_sources_type'])) {
            //信息来源
            $where['information_sources_type'] =  ['like', '%' . trim($param['information_sources_type']) . '%'];
        }
        $room_model = new StudentModel();
        $data = $room_model->getStudents1($where);

        if(empty($data))
        {
            $this->error('此学生没有课程可以导出');
        }

        $objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet
        $objSheet->setTitle("班级的的学生访问表");//给当前活动sheet起个名称
        // Set properties
        $objPHPExcel->getProperties()->setCreator("ctos")
            ->setLastModifiedBy("ctos")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        //设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(15);



        $objPHPExcel->getActiveSheet()->getStyle('A1:M1')->getFont()->setSize(14)->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A2:M220')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objSheet->setCellValue("A1", "编号")->setCellValue("B1", "学号")->setCellValue("C1", "老学号")
            ->setCellValue("D1", "姓名")->setCellValue("E1", "性别")->setCellValue("F1", "	生日")
            ->setCellValue("G1", "所在学校")
            ->setCellValue("H1", "家长姓名")
            ->setCellValue("I1", "家长手机")
            ->setCellValue("J1", "联系地址")
            ->setCellValue("K1", "	助学金")
            ->setCellValue("L1", "	课程")
            ->setCellValue("M1", "班级名字")
            ->setCellValue("N1", "入班时间")
            ->setCellValue("O1", "总费用(元)")
            ->setCellValue("P1", "实付金额(元)")
            ->setCellValue("Q1", "发票")
            ->setCellValue("R1", "状态")
            ->setCellValue("S1", "课时情况");





        //填充数据
        $j = 2;

        foreach ($data as $key => $val) {
            if ($val['gender'] == 1) {
                $val['gender'] = '男';

            } elseif ($val['gender'] == 0) {
                $val['gender'] = '保密';
            } else {
                $val['gender'] = '女';
            }
            if(empty($val['cname']))
            {
                $val['cname']='';
            }
            if(empty($val['bill_status']))
            {
                $val['bill_status']='无';
            }else{
                $val['bill_status']='有';
            }

            if(empty($val['class_status']))
            {
                $val['class_status']='未开课';
            }else if($val['class_status']) {
                $val['class_status']='进行中';
            }else
            {
                $val['class_status']='结课';
            }


                $val['progress']=$val['progress'].'/'.$val['course_hour'];

            $phpexcel_date_format = array(\PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD, \PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2, \PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY, \PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH, \PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS, \PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS, \PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS, \PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7, \PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8, \PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH, \PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14, \PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15, \PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16, \PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17, \PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22);

            $objSheet->setCellValue("A" . $j, $val['id'])->setCellValue("B" . $j, $val['code'])->setCellValue("C" . $j, $val['old_code'])
                ->setCellValue("D" . $j, $val['name'])->setCellValue("E" . $j, $val['gender'])->setCellValue("F" . $j, $val['birthday'])
                ->setCellValue("G" . $j, $val['school_name'])
                ->setCellValue("H" . $j, $val['realname'])
                ->setCellValue("I" . $j, $val['mobile'])
                ->setCellValue("J" . $j, $val['address'])
                ->setCellValue("K" . $j, $val['award_type'])
               ->setCellValue("L" . $j, $val['course_name'])
                ->setCellValue("M" . $j, $val['cname'])
                ->setCellValue("N" . $j, $val['insert_class_time'])
                ->setCellValue("O" . $j, $val['pay_amount'])
                ->setCellValue("P" . $j, $val['amount'])
                 ->setCellValue("Q" . $j, $val['bill_status'])
                ->setCellValue("R" . $j, $val['class_status'])
                ->setCellValue("S" . $j, $val['progress']);



            $objSheet->setCellValue('F'.($j), \PHPExcel_Shared_Date::PHPToExcel($val['birthday']));
            $objSheet->getStyle('F'.($j))->getNumberFormat()->setFormatCode($phpexcel_date_format[1]);

            $objSheet->setCellValue('N'.($j), \PHPExcel_Shared_Date::PHPToExcel($val['insert_class_time']));
            $objSheet->getStyle('N'.($j))->getNumberFormat()->setFormatCode($phpexcel_date_format[1]);
            $j++;
        }
        $count=$room_model->getStudentsWhere($where);
        $total=$room_model->total();
        //设置水平居中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$j.":K".$j)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->mergeCells('A'.$j.":K".$j);
        $objSheet->setCellValue("A" . $j,$count."/".$total);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');//生成excel文件
        //$objWriter->save($dir."/export_1.xls");//保存文件

        // Set active sheet index to the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        ob_end_clean();//清除缓冲区,避免乱码

        // Redirect output to a client’s web browser (Excel5)
        header('Content-Type: application/vnd.ms-excel');
        $filename = '学员导出' . '(' . date('Ymd-His') . ').xls';
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');


    }

    public function browser_export($type, $filename)
    {
        if ($type == "Excel5") {
            header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
        }
        header('Content-Disposition: attachment;filename="' . $filename . '"');//告诉浏览器将输出文件的名称
        header('Cache-Control: max-age=0');//禁止缓存
    }
    public  function ajax_all()
    {
            $student_id=$this->request->param('student_id',0);
            $type=$this->request->param('type',0);
            if(empty($type) || empty($student_id))
            {
                $json_data = [
                    'code' => 0,
                    'msg' => '没有更多数据',
                    'data' => '没有更多数据'
                ];
                return json_encode($json_data);
            }
        if($type==2)
        {
            $data=Db::name('student')->where(['id'=>$student_id])->value('information_sources_teacher');
        }elseif($type==7)
        {
            $data=Db::name('student')->where(['id'=>$student_id])->value('information_sources_student');
        }elseif($type==8)
        {
            $data=Db::name('student')->where(['id'=>$student_id])->value('information_sources_friend');

        }else{
            $data=Db::name('student')->where(['id'=>$student_id])->value('information_sources_other');
        }
        $json_data = [
            'code' => 1,
            'msg' => '有数据',
            'data' =>$data
        ];
        return json_encode($json_data);

    }
}